package register;

import java.sql.*;

public class Search {
	private static String sql;

	/**
	 * @查询全部
	 * 
	 */
	public static StringBuffer searchAll(int size, int page) {
		StringBuffer all = new StringBuffer();
		Connection con = null;
		ResultSet rs = null;
		ResultSetMetaData meta = null;
		Statement ment = null;
		try {
			con = JdbcTools.getConn();

			// 调用Connection的createStatement方法，创建一个Statement对象，用来传递sql语句到数据库；
			ment = con.createStatement();
			String sql = "select * from employee limit " + size + " offset "
					+ (page - 1) * size;
			// 调用Statement的executeQuery方法，传一个sql语句；得到ResultSet结果集;
			rs = ment.executeQuery(sql);
			meta = rs.getMetaData();
			int cols = meta.getColumnCount();
			while (rs.next()) {
				for (int i = 1; i <= cols; i++) {
					// all.append(meta.getColumnName(i) + " ;");
					all.append(rs.getString(i) + "  \t");
				}
				all.append("\n");
			}
			System.out.println(all.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcTools.close(rs, ment, con);
		}

		return all;
	}

	/**
	 * @按条件查询
	 * 
	 */
	public static StringBuffer searchCon(String condition, String cond,
			int size, int page) {
		StringBuffer dim = new StringBuffer();
		Connection con = null;
		ResultSet rs = null;
		ResultSetMetaData meta = null;
		PreparedStatement ps = null;

		if ("按姓查询".equals(condition)) {
			sql = "select * from employee where name like '" + cond + "%' limit "
					+ size + " offset " + (page - 1) * size;
			System.out.println(sql);
		} else if ("按工资排序查询".equals(condition)) {
			sql = "select * from employee order by salary " + " limit " + size
					+ " offset " + (page - 1) * size;
		} else if ("按部门查询".equals(condition)) {
			sql = "select * from employee where dept='" + cond + "' limit " + size
					+ " offset " + (page - 1) * size;
		}
		try {
			con = JdbcTools.getConn();

			// 调用Connection的创建一个preparedStatement 对象来将 SQL 语句发送到数据库。
			ps = con.prepareStatement(sql);
			// 调用Statement的executeQuery方法，传一个sql语句；得到ResultSet结果集;
			rs = ps.executeQuery();
			meta = rs.getMetaData();
			int cols = meta.getColumnCount();
			while (rs.next()) {
				for (int i = 1; i <= cols; i++) {
					dim.append(rs.getString(i) + "  \t");
				}
				dim.append("\n");

				if (rs.isAfterLast()) {
					dim.append("没有该员工!");
					return dim;
				}
			}

			System.out.println(dim.toString());
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JdbcTools.close(rs, ps, con);
		}

		return dim;
	}

	/**
	 * @按工资查询
	 * 
	 */
	public void searchSalary() {

	}

	public void searchAge() {

	}

}